package com.ysl.dao;

import com.ysl.bean.Condition;
import com.ysl.bean.UserDemo;
import com.ysl.utils.DruidManager;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class UserDaoImpl implements UserDao{
    QueryRunner qr = new QueryRunner(DruidManager.getDataSource());
    @Override
    public int insertUser(UserDemo user) throws Exception {
        String sql = "insert into userdemo values(null,?,?,?,?,?,?,?)";
        int update = qr.execute(sql, user.getUserName(), user.getUserPwd(), user.getSex(),
                user.getChk(), user.getSel(), user.getAddress(), user.getBirthday());
        return update;
    }
    @Override
    public UserDemo selectUser(String username, String userpwd) throws Exception {
        String sql = "select * from userdemo where userName = ? and userPwd = ?";
        UserDemo user = qr.query(sql, new BeanHandler<UserDemo>(UserDemo.class), username, userpwd);
        return user;
    }

    @Override
    public UserDemo selectUserId(int id) throws Exception {
        String sql = "select * from userdemo where id = ?";
        UserDemo user = qr.query(sql, new BeanHandler<UserDemo>(UserDemo.class),id);
        return user;
    }

    @Override
    public int updateUser(UserDemo user) throws Exception {
        String sql = "update userdemo set userName=?,userPwd=?,sex =?,chk=?,sel=?,address=?,birthday=? where id = ?";
        List list = Arrays.asList(user.getUserName(),user.getUserPwd(),user.getSex(),
                user.getChk(),user.getSel(),user.getAddress(),user.getBirthday(),user.getId());
        int update = qr.execute(sql, list.toArray());
        return update;
    }

    @Override
    public Integer selectUsersCount(Condition condition) throws Exception {
        String sql = "select count(*) from userdemo inner join education on userdemo.`sel` = education.`eid` where 1 = 1 ";
        List<String> list = new ArrayList<>();
        if (condition.getUsername() != null && !condition.getUsername().equals("")){
            sql += " and userName like ?";
            list.add("%"+condition.getUsername()+"%");
        }
        if (condition.getEid() != null && !condition.getEid().equals("")){
            sql += " and sel = ?";
            list.add(condition.getEid());
        }
        if (condition.getStackdate() != null && !condition.getStackdate().equals("")){
            sql += "and birthday > ?";
            list.add(condition.getStackdate());
        }
        if (condition.getEnddate() != null && !condition.getEnddate().equals("")){
            sql += "and birthday < ?";
            list.add(condition.getEnddate());
        }
        System.out.println("sql = " + sql);
        Long usernum = qr.query(sql, new ScalarHandler<>(), list.toArray());

        return usernum.intValue();
    }

    @Override
    public List<UserDemo> selectUsers(Condition condition, Integer p,Integer pageSize) throws Exception {
        String sql = "select * from userdemo inner join education on userdemo.`sel` = education.`eid` where 1 = 1 ";
        List list = new ArrayList<>();
        if (condition.getUsername() != null && !condition.getUsername().equals("")){
            sql += " and userName like ?";
            list.add("%"+condition.getUsername()+"%");
        }
        if (condition.getEid() != null && !condition.getEid().equals("")){
            sql += " and sel = ?";
            list.add(condition.getEid());
        }
        if (condition.getStackdate() != null && !condition.getStackdate().equals("")){
            sql += "and birthday > ?";
            list.add(condition.getStackdate());
        }
        if (condition.getEnddate() != null && !condition.getEnddate().equals("")){
            sql += "and birthday < ?";
            list.add(condition.getEnddate());
        }
        sql += " limit ?,? ";
        int a = (p-1)*pageSize;
        list.add(a);
        list.add(pageSize);
        System.out.println(" sql = " + sql);
        System.out.println(a);
        System.out.println(pageSize);
        List<UserDemo> users = qr.query(sql,new BeanListHandler<UserDemo>(UserDemo.class), list.toArray());
        return users;
    }

    @Override
    public UserDemo selectUserName(String username) throws Exception {
        String sql = "select * from userdemo where userName = ?";
        UserDemo userDemo = qr.query(sql, new BeanHandler<UserDemo>(UserDemo.class), username);
        return userDemo;
    }

    @Override
    public int deleteUser(int id) throws Exception {
        String sql = "delete from userdemo where id = ?";
        int update = qr.update(sql, id);
        return update;
    }

//    @Override
//    public List<UserDemo> selectAllUser() throws Exception {
//        String sql = "select * from userdemo inner join education on userdemo.`sel` = education.`eid` where 1 = 1 ";
//        and userName = ? and sel = ? and address = ? and birthday > ? and birthday < ?
//
//        List<UserDemo> query = qr.query(sql, new BeanListHandler<UserDemo>(UserDemo.class));
//        return query;
//    }


}
